﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using Aden.Model;
using Aden.Model.MastData;
using Aden.Model.Stock;
using Aden.Model.SOCommon;
using Aden.Util.Common;
using Aden.Util.Database;
using System.Data;
using Aden.DAL.StockData;

namespace Aden.DAL.StockData
{
    public class StockDataFactory
    {
        public List<DateSuppliers> SupplierDate(string costCenterCode, OrderType orderType)
        {
            // 消耗日期列表
            if (orderType.guid == "8FD9674B-3177-4F94-B826-F165448D370D")
                return GetOutDates(costCenterCode);
            // 采购入库日期-供应商列表
            else if (orderType.guid == "259724C2-7979-45DC-BB40-19F12D50ABB9")
                return GetInDates(costCenterCode);
            // 每日餐次
            else if (orderType.guid == "86BF9D51-CFEB-4A28-8910-D698CFEBCA17")
                return GetDailyTicketDates(costCenterCode);
            return null;
        }
        /// <summary>
        /// 每日餐次的日期列表
        /// </summary>
        /// <param name="costCenterCode"></param>
        /// <returns></returns>
        public List<DateSuppliers> GetDailyTicketDates(string costCenterCode)
        {
            string sqlAllDates = " SELECT DISTINCT A.ORDERDATE AS DATE, "
               + " -15 AS LEADDAYOFDAILYTICKETS "
               //+ " ISNULL(B.LEADDAYOFDAILYTICKETS,0) AS LEADDAYOFDAILYTICKETS "
               + " FROM TBLORDERHEAD (NOLOCK) A "
               //+ ", CCMAST (NOLOCK) B "
               + " WHERE A.COSTCENTERCODE = '{0}' AND A.ACTIVE = 1 "
               + " AND CONVERT(VARCHAR(10),A.ORDERDATE,23) <= CONVERT(VARCHAR(10), GETDATE(), 23) "
               + $" AND A.COSTCENTERCODE = '{costCenterCode}' "
               + " ORDER BY A.ORDERDATE ";
           
            sqlAllDates = string.Format(sqlAllDates, costCenterCode);
            List<DateSuppliers> AllDates = SqlServerHelper.GetEntityList<DateSuppliers>(SqlServerHelper.salesorderConn(), sqlAllDates);
            // 每日餐次的最大日期为今天   
            DateTime maxDate = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 00:00:00.000"));
            // 每日餐次起始日期
            int leadDayofDailyTickets = AllDates.FirstOrDefault() == null ? 0 : AllDates.FirstOrDefault().leadDayofDailyTickets;
            // 数列中的最小日期
            DateTime minDate = maxDate.AddDays(leadDayofDailyTickets);
            var dt = (new StockTrans(costCenterCode)).GetLastOutDate();
            if (dt == null)
                return null;
            // 取消最小日期为最后一个收货日期
            minDate = DateTime.Compare((DateTime)dt, minDate)>0 ? minDate : (DateTime)dt;
            
            AllDates = AllDates.Where(dr => dr.date >= minDate).ToList<DateSuppliers>();
            if (AllDates == null || !AllDates.Any())
                return null;

            for (DateTime date = minDate; date <= maxDate; date = date.AddDays(1.0))
            {
                // 去重
                if (AllDates.Any(dr => dr.date == date))
                    continue;
                // 补全遗漏日期
                DateSuppliers dates = new DateSuppliers();
                dates.date = date;
                AllDates.Add(dates);
            }
           
            string sql = "SELECT DISTINCT TRANSDATE AS DATE FROM TBLTRANSDATA (NOLOCK) "
                + " WHERE COSTCENTERCODE = '{0}' AND BUSINESSTYPE = 'DailyTicket' AND DELETEUSER IS NULL";
            sql = string.Format(sql, costCenterCode);
            List<DateSuppliers> SavedDates = SqlServerHelper.GetEntityList<DateSuppliers>(SqlServerHelper.salesorderConn(), sql);
            if (SavedDates == null || !SavedDates.Any())
                return AllDates.Distinct(dr => dr.date).OrderBy(dr => dr.date).ToList();
            //var datas = AllDates.GroupJoin(SavedDates, mos => mos.date, nos => nos.date,
            //    (mos, nos) => new { mos, nos }).Where(dr=> dr.nos == null || !dr.nos.Any())
            //    .Select(dr=>dr.mos).Distinct(dr=>dr.date).OrderBy(dr=>dr.date).ToList();
            var datas = AllDates.GroupJoin(SavedDates, mos => mos.date, nos => nos.date,
                (mos, nos) => new { mos, nos }).Select(dr =>
                {
                    if (dr.nos != null && dr.nos.Any())
                    {
                        dr.mos.remark = "(Submitted Already)";
                    }
                    return dr.mos;
                }).Distinct(dr => dr.date).OrderBy(dr => dr.date).ToList();
            if (datas != null || datas.Any())
                return datas;
            return null;
        }

        /// <summary>
        /// 采购入库日期 - 供应商列表
        /// </summary>
        /// <param name="costCenterCode"></param>
        /// <returns></returns>
        private List<DateSuppliers> GetInDates(string costCenterCode)
        {
            List<DateSuppliers> listSupplierDate = new List<DateSuppliers>();

            string sqlMOLine = "WITH A AS ( "
                + "(SELECT MAX(ISNULL(TRANSDATE,GETDATE())) AS DATE FROM TBLTRANSDATA (NOLOCK) WHERE QTY < 0 AND COSTCENTERCODE='{0}' AND ITEMCOST IS NOT NULL)) "
                + " SELECT DISTINCT ORDERDATE AS DATE, SUPPLIERCODE AS CODE, '' AS NAME, "
                + " APPTYPE FROM TBLORDERLINES (NOLOCK) "
                + " WHERE COSTCENTERCODE = '{0}' AND DELETETIME IS NULL "
                + " AND STATUS in ('CREATED','SUBMITTED') AND ISNULL(SUPPLIERCODE,'')<>'' "
                + " AND CONVERT(VARCHAR(10),ORDERDATE,23) >= ISNULL((SELECT DATE FROM A), "
                + " CONVERT(VARCHAR(10),ORDERDATE,23)) AND CONVERT(VARCHAR(10),ORDERDATE,23) <= CONVERT(VARCHAR(10), GETDATE(), 23) "
                + " AND ((FROMTYPE= 'MENU' AND BUSINESSTYPE<>'PO') OR FROMTYPE = 'NEW') "
                + " ORDER BY ORDERDATE,APPTYPE,SUPPLIERCODE ";

            string sqlSuppliers = "SELECT SUPPLIER_CODE AS CODE, SUPPLIER_NAME AS NAME FROM [DBO].[D_SUPPLIER] (NOLOCK) ";

            // 所有的待入库的供应商-日期组合
            sqlMOLine = string.Format(sqlMOLine, costCenterCode);

            var DataSupplierDate = SqlServerHelper.GetDataTable(SqlServerHelper.salesorderConn(), sqlMOLine);
            if (DataSupplierDate == null || !DataSupplierDate.AsEnumerable().Any())
                return null;
            // 供应商明细
            var SupplierList = SqlServerHelper.GetDataTable(SqlServerHelper.purchasepriceconn(), sqlSuppliers);
            // 赋值供应商名字 + label 
            var data = DataSupplierDate.AsEnumerable().GroupJoin(SupplierList.AsEnumerable(),
                 mos => mos.Field<string>("CODE"), nos => nos.Field<string>("CODE"),
                 (mos, nos) => new { mos, nos }).Select(dr =>
                 {
                     var supplierName = string.Empty;
                     if (dr.nos != null && dr.nos.Any())
                         supplierName = dr.nos.FirstOrDefault().Field<string>("name");
                     string weekday = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.
                                    GetDayName(dr.mos.Field<DateTime>("DATE").DayOfWeek);
                     return new
                     {
                         date = dr.mos.Field<DateTime>("DATE"),
                         label = dr.mos.Field<DateTime>("DATE").ToString("yyyy-MM-dd") + ' ' + weekday + "-"
                                + (dr.mos.Field<string>("APPTYPE") == "normalMenu" ? "1" :
                                (dr.mos.Field<string>("APPTYPE") == "additionMenu" ? "2" : "3")),
                         appType = dr.mos.Field<string>("APPTYPE"),
                         code = dr.mos.Field<string>("CODE"),
                         name = supplierName,

                     };
                 }).ToList();

            listSupplierDate = data.GroupBy(dr => new { dr.date, dr.appType, dr.label }).Select(dr => new DateSuppliers()
            {
                date = dr.Key.date,
                label = dr.Key.label,
                appType = dr.Key.appType,
                suppliers = dr.Select(ds => new Supplier()
                {
                    code = ds.code,
                    name = ds.name
                }).Distinct().ToList<Supplier>()
            }).OrderBy(dr => dr.label).ToList();

            return listSupplierDate;
        }

        public string sqlGetOutDatas(string costCenterCode)
        {
            //最后一个出库日期
            string sql = "SELECT 1 ID, MAX(TRANSDATE) ORDERDATE FROM TBLTRANSDATA (NOLOCK) "
                + "WHERE  COSTCENTERCODE = '{0}' AND QTY< 0 AND ITEMCOST IS NOT NULL";

            //最早的入库日期
            sql += " UNION ALL " +
                "SELECT 2 ID, MIN(TRANSDATE) ORDERDATE FROM tblTransData (NOLOCK) "
                + "WHERE  COSTCENTERCODE = '{0}' AND QTY> 0 AND ITEMCOST IS NOT NULL";

            //未入库的日期
            sql += " UNION ALL " +
                "SELECT 3 ID, MIN(ORDERDATE) ORDERDATE FROM TBLORDERLINES (NOLOCK) " +
                "WHERE COSTCENTERCODE='{0}' AND DELETETIME IS NULL AND STATUS IN ('CREATED','SUBMITTED') ";

            sql = string.Format(sql, costCenterCode);

            return sql;
        }

        /// <summary>
        /// 消耗日期列表
        /// </summary>
        /// <param name="costCenterCode"></param>
        /// <returns></returns>
        public List<DateSuppliers> GetOutDates(string costCenterCode)
        {
            string sql = sqlGetOutDatas(costCenterCode);

            var dt = SqlServerHelper.GetDataTable(SqlServerHelper.salesorderConn(), sql);
            if (dt == null || !dt.AsEnumerable().Any()) return null;
            //取值的方法                         
            Func<int, DateTime> funcDate = (id) =>
            {
                DataRow[] drs = dt.Select(string.Format("ID={0}", id));
                if (drs == null || drs.Length == 0)
                    throw new Exception("");

                string tmp = drs[0]["ORDERDATE"].ToStringTrim();
                if (!string.IsNullOrWhiteSpace(tmp))
                    return DateTime.Parse(tmp);

                throw new Exception("");
            };
            //存在未入库的order
            DateTime date2 = DateTime.Now;
            try
            {
                date2 = funcDate(3);
            }
            catch
            {
                date2 = DateTime.Now;
            }

            DateTime date1 = DateTime.Now;
            try
            {    //找到最后一个出库日
                date1 = funcDate(1);

                //检查该出库日，是否在出库后做过入库
                //sql = "SELECT TOP 1 A2.ID FROM tblTransData (NOLOCK) A1, tblTransData (NOLOCK) A2 "
                //+ "WHERE  A1.COSTCENTERCODE = '{0}' AND A1.TRANSDATE='{1}' AND A2.COSTCENTERCODE = '{0}' AND A2.TRANSDATE='{1}' " +
                //"AND A1.QTY<0 AND A2.QTY>0 AND A1.CREATETIME<A2.CREATETIME "
                //+" GROUP BY A2.ID HAVING MAX(A1.CREATETIME) < MAX(A2.CREATETIME)";
                sql = "SELECT TOP 1 1 FROM (SELECT MAX(CREATETIME) CREATETIME FROM TBLTRANSDATA(NOLOCK) "
                    + " WHERE  COSTCENTERCODE = '{0}' AND TRANSDATE = '{1}' AND QTY < 0 AND ITEMCOST IS NOT NULL) A1, "
                    + " (SELECT MAX(CREATETIME) CREATETIME FROM TBLTRANSDATA (NOLOCK) "
                    + " WHERE COSTCENTERCODE = '{0}' AND TRANSDATE = '{1}' AND QTY> 0 AND ITEMCOST IS NOT NULL) A2 "
                    + " WHERE  A1.CREATETIME < A2.CREATETIME ";


                sql = string.Format(sql, costCenterCode, date1.ToString("yyyy-MM-dd"));
                dt = SqlServerHelper.GetDataTable(SqlServerHelper.salesorderConn(), sql);

                StockParam param = new StockParam();
                param.costCenterCode = costCenterCode;
                param.date = date1.ToString("yyyy-MM-dd");
                List<productDescData> productDescOption = GetProductDescOptions(param);
                sql = " SELECT DISTINCT MENUPRODUCTDESC1 AS PRODUCTNAME FROM TBLTRANSDATA (NOLOCK) "
                    + " WHERE COSTCENTERCODE = '{0}' AND TRANSDATE = '{1}' AND QTY < 0 AND ITEMCOST IS NOT NULL";
                sql = string.Format(sql, costCenterCode, param.date);
                List<productDescData> consumedProDesc = SqlServerHelper.GetEntityList<productDescData>(SqlServerHelper.salesorderConn(), sql);

                try
                {
                    var remainedProDesc = productDescOption.GroupJoin(consumedProDesc,
                            mos => mos.productName, nos => nos.productName, (mos, nos) => new { mos, nos }).
                            Where(dr => !dr.nos.Any()).Select(dr => dr.mos).ToList();
                    // 如果有未消耗餐次种类，则当天可多次消耗
                    if (remainedProDesc != null && remainedProDesc.Any())
                        date1 = date1.AddDays(0);
                    else if (dt == null || !dt.AsEnumerable().Any())
                        date1 = date1.AddDays(1);
                }
                catch
                {
                    if (dt == null || !dt.AsEnumerable().Any())
                        date1 = date1.AddDays(1);
                }
            }
            catch
            {   //没有做过出库
                try
                {
                    date1 = funcDate(2);
                }
                catch
                {
                    //没有做过入库
                    return null;
                }
            }

            List<DateSuppliers> listSupplierDate = new List<DateSuppliers>();
            for (DateTime d = date1; d < date2; d = d.AddDays(1))
            {
                listSupplierDate.Add(new DateSuppliers
                {
                    date = d
                });
            }
            return listSupplierDate;
        }
        /// <summary>
        /// 获取仓库管理类型: 入库、出库、消耗、转仓
        /// </summary>
        /// <param name="Guid"></param>
        /// <returns></returns>
        public List<OrderType> GetOrderType(string orderName)
        {
            string sql = "SELECT * FROM [dbo].[tblTransType] (NOLOCK) "
                + "WHERE ORDERNAME ='{0}' AND STATUS ='1'";
            sql = string.Format(sql, orderName);
            List<OrderType> OrderTypeList = SqlServerHelper.GetEntityList<OrderType>(SqlServerHelper.salesorderConn(), sql);
            return OrderTypeList;
        }
        /// <summary>
        /// 库存消耗在各餐次种类下的产品需求量
        /// </summary>
        /// <param name="date"></param>
        /// <param name="costCenterCode"></param>
        /// <param name="productDesc"></param>
        /// <returns></returns>
        public string GetStockQty(DateTime date, string costCenterCode, string productDesc)
        {
            if (string.IsNullOrWhiteSpace(productDesc))
                return string.Empty;
            string sql = " with C as (select A.costCenterCode,isnull(B.requiredDate, A.requiredDate) as RequiredDate, m.productCode, m.name_en as productDesc, B.itemCode, "
                + " sum(B.requiredQty) as Qty from MenuOrderHead (nolock) A, MenuOrderLine(nolock) B, "
                + " (select distinct u.itemGuid, u.productCode,v.name_en from salesOrderItem(nolock) u, productTypeData v where u.ProductCode = v.id) m "
                + " where m.itemGuid = A.SOItemGUID and A.headGuid = B.headGuid and A.deleteTime is null and B.deleteTime is null "
                + " group by A.costCenterCode,m.name_en,B.ItemCode,m.productCode,isnull(B.requiredDate, A.requiredDate) "
                + " union select A.costCenterCode, A.OrderDate as RequiredDate ,B.itemCode as productCode, A.appType as productDesc, A.itemCode, sum(A.Qty) as Qty "
                + " from tblOrderLines (nolock)A, productTypeData(nolock) B where A.appType = 'additionMenu' and B.Name_EN = A.appType and "
                + " A.deleteUser is null and A.DeleteTime is null group by A.costCenterCode,A.itemCode,B.itemCode,A.appType,A.OrderDate "
                + " union select A.costCenterCode,A.OrderDate as RequiredDate,  '' as productCode, '' as productDesc,  A.itemCode, sum(A.Qty) as Qty from tblOrderLines (nolock) A "
                + " where A.deleteUser is null and A.DeleteTime is null and isnull(A.appType, '') = '' group by A.costCenterCode,A.itemCode,A.OrderDate) "
                + " select * from C where C.costCenterCode = '{0}' and C.RequiredDate = '{1}' and C.productDesc='{2}' and not exists ( "
                + " select top 1 1 from tblTransData where CostCenterCode = '{0}' and TRANSDATE =  '{1}' and MenuProductDesc1 = '{2}' and qty < 0)";
            sql = string.Format(sql, costCenterCode, date, productDesc);
            return sql;
        }

        /// <summary>
        /// 读取库存明细数据
        /// </summary>
        /// <param name="costCenterCode"></param>
        /// <param name="date"></param>
        /// <param name="supplierCode"></param>
        /// <param name="customerCode"></param>
        /// <param name="coa"></param>
        /// <param name="orderType"></param>
        /// <returns></returns>
        public List<TransData> GetStockList(DateTime date, string costCenterCode, string productDesc)
        {
            string sql = "select a1.itemcode,a1.uom,a1.businessType,a1.itemcost,a1.totalqty+isnull(a2.outqty,0) stockQty, "
                + "case when (a1.runningqty+isnull(a2.outqty,0))>a1.qty then a1.qty else a1.runningqty+isnull(a2.outqty,0) end qty "
                + "from " + GetStockSQL("{0}", "{1}")
                + "order by a1.itemcode,a1.orderdate,a1.createtime,a1.id";
            string sqlItemName = "select distinct Items_Code as ItemCode, Items_Name as ItemName from [dbo].[d_item] (nolock) ";

            string sqlStockQty = GetStockQty(date, costCenterCode, productDesc);

            sql = string.Format(sql, costCenterCode, date.ToString("yyyy-MM-dd"));
            List<TransData> TransData = SqlServerHelper.GetEntityList<TransData>(SqlServerHelper.salesorderConn(), sql);
            List<TransData> ItemNameList = SqlServerHelper.GetEntityList<TransData>(SqlServerHelper.purchasepriceconn(), sqlItemName);
            List<TransData> StockQty = null;
            //获取各餐次类别下的产品需求量
            if (!string.IsNullOrWhiteSpace(sqlStockQty))
            {
                StockQty = SqlServerHelper.GetEntityList<TransData>(SqlServerHelper.salesorderConn(), sqlStockQty);
            }
            // 再看看
            List<TransData> data = TransData.
                GroupBy(q => new { q.itemCode, q.stockQty }).
                Select(q =>
                {
                    int x = 0;
                    decimal price = 0;
                    var tmp = q.Select(p =>
                    {
                        if (price != p.itemCost)
                        {
                            x++;
                            price = p.itemCost??0;
                        }

                        return new
                        {
                            type = x,
                            price = p.itemCost,
                            qty = p.qty
                        };
                    });
                    TransData d = q.FirstOrDefault();
                    d.lines = new List<TransLine>();
                    d.lines.AddRange(tmp.GroupBy(p => new { p.type, p.price }).Select(p => new TransLine
                    {
                        itemCost = p.Key.price??0,
                        stockQty = p.Sum(l => l.qty)??0
                    }));

                    return d;
                }).ToList();
            if (ItemNameList != null && ItemNameList.Count > 0 && data.Count > 0)
            {
                data = data.GroupJoin(ItemNameList, mos => mos.itemCode, nos => nos.itemCode, (mos, nos) =>
                {
                    mos.itemName = nos.FirstOrDefault().itemName;
                    return mos;
                }).ToList();
            }

            if (StockQty != null && StockQty.Count > 0 && data.Count > 0)
            {
                data = data.GroupJoin(StockQty, mos => mos.itemCode, nos => nos.itemCode, (mos, nos) => new { mos, nos }).
                    Where(dr => dr.nos.Any()).Select(dr =>
                    {

                        dr.mos.qty = dr.nos.FirstOrDefault() == null ? 0 : dr.nos.FirstOrDefault().qty;
                        return dr.mos;
                    }).ToList();
            }
            return data.ToList(); //SqlServerHelper.GetEntityList<TransData>(SqlServerHelper.salesorderConn(), sqlMain);
        }
        /// <summary>
        /// 读取每日餐次
        /// </summary>
        /// <param name="date"></param>
        /// <param name="costCenterCode"></param>
        /// <returns></returns>
        public List<TransData> GetDailyTicketList(DateTime date, string costCenterCode)
        {
            string sql = "SELECT DISTINCT A.ITEMGUID AS ORDERLINEGUID, C.ITEMCODE ,C.NAME_EN + '-'+A.PRODUCTDESC AS ITEMNAME, " +
                " A.PRICE AS ITEMPRICE, 'PC' AS UOM, " +
                " C.NAME_EN as MAINTYPE ,A.COSTCENTERCODE, CAST(1 AS BIT) AS ISSERVICEITEM " +
                " FROM [DBO].[SALESORDERITEM] (NOLOCK) A, SALESORDERHEAD (NOLOCK) B,[DBO].[PRODUCTTYPEDATA] (NOLOCK) C " +
                " WHERE C.ID=A.PRODUCTCODE AND A.COSTCENTERCODE= '{0}' AND A.HEADGUID = B.HEADGUID " +
                " AND A.STATUS<>9 AND A.STATUS<>0 AND B.STATUS<>9 AND B.STATUS<>0 and '{1}' " +
                " between A.StartDate and A.EndDate and '{1}' between B.StartDate and B.EndDate";
            sql = string.Format(sql, costCenterCode, date.ToString("yyyy-MM-dd"));
            List<TransData> DailyTicketList = SqlServerHelper.GetEntityList<TransData>(SqlServerHelper.salesorderConn(), sql);
            return DailyTicketList;
        }

        private string GetStockSQL(string costCenterCode, string date, string itemCode = "")
        {
            string sql = "(select itemcode,TRANSDATE,qty,itemcost,createtime,id,uom,businessType,sum(qty) over(partition by itemcode "
                + "order by TRANSDATE,createtime,id) as runningqty, "
                + "sum(qty) over(partition by itemcode) as totalqty from tblTransData (nolock) "
                + "where itemcost is not null and qty>0 and costcentercode='{0}' and TRANSDATE<='{1}' {2}) a1 "
                + "left join (select itemcode,sum(qty) outqty from tblTransData (nolock) "
                + "where itemcost is not null and qty<0  and costcentercode='{0}' and TRANSDATE<='{1}' {2} group by itemcode) a2 "
                + "on {3} " +
                "where a1.runningqty+isnull(a2.outqty,0)>0 ";

            sql = string.Format(sql, costCenterCode, date,
                string.IsNullOrEmpty(itemCode) ? "" : string.Format(" and itemcode='{0}'", itemCode),
                string.IsNullOrEmpty(itemCode) ? "a1.itemcode=a2.itemcode" : "1=1");
            return sql;
        }

        /// <summary>
        /// 出入库Item 再看一下
        /// </summary>
        /// <param name="date"></param>
        /// <param name="costCenterCode"></param>
        /// <param name="supplierCode"></param>
        /// <param name="customerCode"></param>
        /// <param name="coa"></param>
        /// <param name="orderType"></param>
        /// <returns></returns>
        public List<TransData> GetTransactionDataList(DateTime date, string costCenterCode,
            string supplierCode, string customerCode, string coa, OrderType orderType, string appType, string productDesc)
        {
            if (orderType.transType != null)
            {
                // 待入库Item
                if (orderType.guid == "259724C2-7979-45DC-BB40-19F12D50ABB9")
                    return GetStockInItemList(date, costCenterCode, supplierCode, appType);
                // 待出库Item
                else if (orderType.guid == "8FD9674B-3177-4F94-B826-F165448D370D")
                    return GetStockList(date, costCenterCode, productDesc);
                // 每日餐次
                else if (orderType.guid == "86BF9D51-CFEB-4A28-8910-D698CFEBCA17")
                    return GetDailyTicketList(date, costCenterCode);
            }
            return null;
        }
        /// <summary>
        /// sql - 待入库Item
        /// </summary>
        /// <param name="costCenterCode"></param>
        /// <param name="date"></param>
        /// <param name="supplierCode"></param>
        /// <param name="appType"></param>
        /// <returns></returns>
        private string GetStockInItemSql(string costCenterCode, string date, string supplierCode, string appType)
        {
            string sql = "SELECT A1.HEADGUID AS ORDERLINEGUID, A1.ITEMCODE, A1.UOM, ROUND(SUM(A1.QTY),6) AS QTY, A1.ITEMPRICE, "
                + "A1.BUSINESSTYPE, A1.FROMTYPE  "
                + "FROM TBLORDERLINES(NOLOCK) A1 "
                + "WHERE A1.COSTCENTERCODE = '{0}' AND A1.ORDERDATE = '{1}' AND A1.SUPPLIERCODE = '{2}' AND A1.APPTYPE='{3}' "
                + "AND A1.DELETETIME IS NULL AND A1.STATUS IN ('CREATED','SUBMITTED') "
                + "GROUP BY A1.HEADGUID, A1.GUID, A1.ITEMCODE,A1.UOM,A1.ITEMPRICE, A1.BUSINESSTYPE, A1.FROMTYPE "
                + "HAVING NOT EXISTS (SELECT TOP 1 1 FROM TBLTRANSDATA WHERE COSTCENTERCODE = '{0}' AND SUPPLIERCODE ='{2}' "
                + "AND ORDERLINEGUID = A1.GUID AND ITEMCOST IS NOT NULL)";
            sql = string.Format(sql, costCenterCode, date, supplierCode, appType);
            return sql;
        }

        /// <summary>
        /// 待入库Item - 界面不需要显示期初 Stock 和 purchasePrice
        /// </summary>
        /// <param name="date"></param>
        /// <param name="costCenterCode"></param>
        /// <param name="supplierCode"></param>
        /// <returns></returns>
        public List<TransData> GetStockInItemList(DateTime date, string costCenterCode, string supplierCode, string appType)
        {

            string sql = "WITH C AS ({0}) "
                + "SELECT D.ORDERLINEGUID, C.ITEMCODE,C.UOM, SUM(C.QTY) AS QTY, C.ITEMPRICE,D.BUSINESSTYPE FROM C "
                + "LEFT JOIN(SELECT C.ORDERLINEGUID, C.ITEMCODE, C.FROMTYPE, C.BUSINESSTYPE FROM C "
                + "WHERE (C.FROMTYPE= 'MENU' AND C.BUSINESSTYPE<>'PO') OR C.FROMTYPE = 'NEW') D "
                + "ON C.ITEMCODE = D.ITEMCODE AND C.FROMTYPE = D.FROMTYPE "
                + "GROUP BY C.ITEMCODE,C.UOM, C.ITEMPRICE,D.ORDERLINEGUID,D.BUSINESSTYPE";
            // GetStockInItemSql
            string strStockItem = GetStockInItemSql(costCenterCode, date.ToString("yyyy-MM-dd"), supplierCode, appType);
            sql = string.Format(sql, strStockItem);
            // Item Name
            string sqlItemName = "select distinct Items_Code as ItemCode, Items_Name as ItemName from [dbo].[d_item] (nolock) ";
            List<TransData> ItemNameList = SqlServerHelper.GetEntityList<TransData>(SqlServerHelper.purchasepriceconn(), sqlItemName);
            // 获取Purchase Order  - Menu Part 调整项的orderLineGUID和Menu明细项(Submitted)的orderLineGUID，用于收货后插入TblTransData
            List<string> orderLineGUIDtoTblTransData = SqlServerHelper.GetEntityList<TransData>(SqlServerHelper.salesorderConn(),
                strStockItem).Select(dr => dr.orderLineGUID).Distinct().ToList();
            // 合并Purchase Order  - Menu Part 调整项的orderLineGUID和Menu明细项
            List<TransData> StockInItemList = SqlServerHelper.GetEntityList<TransData>(SqlServerHelper.salesorderConn(), sql);
            var data = StockInItemList;
            if (StockInItemList != null && StockInItemList.Any())
            {
                data = StockInItemList.GroupJoin(ItemNameList, mos => mos.itemCode,
                     nos => nos.itemCode, (mos, nos) =>
                     {
                         mos.orderLineGUIDtoTblTransData = orderLineGUIDtoTblTransData;
                         mos.itemName = nos.FirstOrDefault().itemName;
                         return mos;
                     }).ToList();
            }
            return data;
        }

        /// <summary>
        /// 上传库存数据 - 采购入库和消耗
        /// </summary>
        /// <param name="orderType"></param>
        /// <returns></returns>
        public int SaveToTransactionData(string date, string costCenterCode, string creater, List<TransData> data, OrderType orderType, string productDesc)
        {
            StringBuilder stringBuilder = new StringBuilder();
            string now = DateTime.Now.ToString();
            string busType = string.Empty;
            string orderLineGUIDtoTblTransData = string.Empty;
            // 入库时Update - Update Statas
            string sqlUpdate = "UPDATE TBLORDERLINES SET STATUS = 'completed',completeUser='{3}',completeTime='{4}' WHERE STATUS IN ('CREATED','SUBMITTED') AND COSTCENTERCODE = '{0}' AND SUPPLIERCODE = '{1}' AND HEADGUID in ('{2}') ; ";
            // 入库时Insert (TransData / guid <==> tblOrderLines / TransDataGuid)
            string sqlin = " INSERT INTO TBLTRANSDATA " +
                           " (ORDERLINEGUID, ITEMCODE,ITEMNAME,SUPPLIERCODE,COSTCENTERCODE,UOM,QTY,ITEMCOST,TRANSDATE,CREATEUSER,BUSINESSTYPE,MENUPRODUCTDESC1) " +
                           " SELECT '{0}', '{1}', N'{2}', '{3}', '{4}', N'{5}', '{6}', '{7}', '{8}' ,N'{9}', N'{10}', N'{11}' " +
                           " WHERE NOT EXISTS (" +
                           " SELECT TOP 1 1 FROM TBLTRANSDATA WHERE COSTCENTERCODE='{4}' AND ITEMCOST IS NOT NULL AND ((QTY<0 AND TRANSDATE>'{8}') OR (TRANSDATE='{8}' AND SUPPLIERCODE='{3}' AND QTY>0 AND ORDERLINEGUID='{0}'))); " +
                           " UPDATE TBLORDERLINES SET GUID='{0}' WHERE HEADGUID in ('{12}') AND ITEMCODE = '{1}'; ";

            // 出库时Insert
            string sqlout = "INSERT INTO TBLTRANSDATA " +
                         " (ORDERLINEGUID, ITEMCODE,ITEMNAME,SUPPLIERCODE,COSTCENTERCODE,UOM,QTY,ITEMCOST,TRANSDATE,CREATEUSER," +
                         " BUSINESSTYPE,MENUPRODUCTDESC1) " +
                         " select '{0}','{1}',N'{2}','{3}','{4}',N'{5}',-(case when {6} >(a1.runningqty + isnull(a2.outqty, 0)) then " +
                         " (case when(a1.runningqty + isnull(a2.outqty, 0)) > a1.qty then a1.qty else a1.runningqty + isnull(a2.outqty, 0) end) " +
                         " else {6} - (case when(a1.runningqty + isnull(a2.outqty, 0)) > a1.qty then a1.runningqty + isnull(a2.outqty, 0) - a1.qty else 0 end) end) qty,a1.itemcost, " +
                         " '{8}' , N'{9}', N'{10}', N'{11}' " +
                         " from " + GetStockSQL("{4}", "{8}", "{1}") +
                         " and not exists (select top 1 1 from tbltransdata where itemcost is not null and qty < 0 and costcentercode = '{4}' " +
                         " and TRANSDATE >'{8}') and  a1.totalqty + isnull(a2.outqty, 0) - {6} >= 0 and " +
                         " (case when(a1.runningqty + isnull(a2.outqty, 0)) > a1.qty then a1.runningqty + isnull(a2.outqty, 0) - a1.qty else 0 end) < {6} " +
                         " group by a1.itemcost,a1.runningqty,a2.outqty,a1.qty ;";
            // Business Type
            var businessTypeList = GetBusinessType(orderType);

            if (!string.IsNullOrEmpty(businessTypeList))
                busType = businessTypeList.ToStringTrim();

            StringBuilder sbsql = new StringBuilder();

            string sql = "";
            if (orderType.guid == "259724C2-7979-45DC-BB40-19F12D50ABB9")
            {
                data.GroupBy(dr => dr.supplierCode).Select(dr =>
                {
                    orderLineGUIDtoTblTransData = string.Join("','", dr.FirstOrDefault().orderLineGUIDtoTblTransData.Select(q => q).Distinct());
                    sbsql.AppendFormat(sqlUpdate, costCenterCode, dr.Key, orderLineGUIDtoTblTransData, creater, now);
                    return dr;
                }).ToList();
                //如果采购接收明细在删单状态，则不要在TBLTransData表中插入数据
                if (!data.Where(dr => dr.completeFlag == "true").Any())
                    sql = sqlin;
            }
            else if (orderType.guid == "8FD9674B-3177-4F94-B826-F165448D370D")
                sql = sqlout;

            data.Select(q =>
            {
                //如果产品量>0且采购接收明细不在删单状态，则对表TblTransData执行插入|| 消耗: qty>0 && (completeFlag == null || completeFlag == true)
                if (q.qty > 0 && (q.completeFlag == "false" || string.IsNullOrWhiteSpace(q.completeFlag)))
                {
                    sbsql.AppendFormat(sql, Guid.NewGuid().ToString(), q.itemCode,
                        q.itemName, string.IsNullOrWhiteSpace(q.supplierCode) ? "" : q.supplierCode, costCenterCode, q.uom,
                        q.qty, q.itemPrice, date, creater, 
                        busType,string.IsNullOrWhiteSpace(productDesc) ? "" : productDesc, orderLineGUIDtoTblTransData);
                }
                return q;
            }).ToList();
            try
            {
                int count = data.Count(q => q.qty > 0 || q.completeFlag == "true").ToInt();
                if (orderType.guid == "259724C2-7979-45DC-BB40-19F12D50ABB9" && count == 0)
                    return 0;
                SqlServerHelper.Execute(SqlServerHelper.salesorderConn(), sbsql.ToString());
                sbsql = new StringBuilder();
                return 1;
            }
            catch 
            {
                return 0;
            }
        }
        private string GetBusinessType(OrderType orderType)
        {
            // Business Type
            string sqlBusinessType = "SELECT BUSINESSTYPE FROM TBLTRANSTYPE (NOLOCK) WHERE ORDERNAME ='{0}' ";
            sqlBusinessType = string.Format(sqlBusinessType, orderType.orderName);
            var businessTypeList = SqlServerHelper.GetDataScalar(SqlServerHelper.salesorderConn(), sqlBusinessType);
            if (businessTypeList != null)
                return businessTypeList.ToStringTrim();
            return string.Empty;
        }
        /// <summary>
        /// 保存每日餐次数据
        /// </summary>
        /// <returns></returns>
        public int SaveDailyTicket(string date, string costCenterCode, string creater, List<TransData> data, OrderType orderType)
        {
            StringBuilder sbsql = new StringBuilder();
            string transType = string.IsNullOrWhiteSpace(orderType.transType) ? "" : orderType.transType;
            // 每日餐次
            string strDailyTicket = "INSERT INTO TBLTRANSDATA (ITEMCODE, ITEMNAME, ORDERLINEGUID,    "
                + "COSTCENTERCODE, UOM, QTY, ITEMPRICE, BUSINESSTYPE, TRANSDATE, CREATEUSER) "
                + "SELECT '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}' "
                + "WHERE NOT EXISTS (SELECT TOP 1 1 FROM TBLTRANSDATA WHERE ITEMCOST IS NULL AND COSTCENTERCODE='{3}' "
                + "AND ((QTY<0 AND TRANSDATE>'{8}') OR (TRANSDATE='{8}' AND ORDERLINEGUID='{2}'))) ";
            var businessTypeList = GetBusinessType(orderType);
            string busType = string.Empty;
            if (!string.IsNullOrEmpty(businessTypeList))
                busType = businessTypeList.ToStringTrim();

            data.Select(q =>
            {
                if (q.qty > 0)
                    sbsql.AppendFormat(strDailyTicket, q.itemCode,
                        q.itemName, q.orderLineGUID, costCenterCode, q.uom,
                        q.qty, q.itemPrice, busType, date, creater);
                return q;
            }).ToList();
            try
            {
                int i = SqlServerHelper.Execute(SqlServerHelper.salesorderConn(), sbsql.ToString());
                return i;
            }
            catch (Exception ex)
            {
                return 0;
            }
        }
        /// <summary>
        /// 库存消耗 - 早、中、晚餐、附加餐下拉框选项
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<productDescData> GetProductDescOptions(StockParam param)
        {
            try
            {

                string sql = " with C as (select distinct A.productCode, D.name_en as productName from "
                    + " [dbo].[SalesOrderItem](nolock) A, menuOrderHead(nolock) B, menuOrderLine(nolock) C, [dbo].[ProductTypeData](nolock) D "
                    + " where A.ItemGUID = B.SOItemGuid and A.status <> 9 and isnull(A.ExpiryDate, '') = ''  and A.productCode = D.ID "
                    + " and B.costCenterCode = '{0}' and B.deletetime is null and C.deletetime is null  and B.HeadGuid = C.HeadGuid "
                    + " and isnull(C.RequiredDate, B.RequiredDate) =  '{1}' "
                    + " union  select distinct B.ItemCode as productCode, A.appType as productName "
                    + " from tblOrderLines (nolock) A, productTypeData (nolock) B "
                    + " where A.appType = B.Name_EN and  A.costcenterCode = '{0}' and A.deletetime is null and A.appType = 'additionMenu'  "
                    + " and A.orderDate =  '{1}') select * from C where C.productName not in ( "
                    + " select distinct MenuProductDesc1 from tblTransData (NOLOCK) where CostCenterCode = '{0}' and TRANSDATE =  '{1}' "
                    + " and qty<0) ";

                sql = string.Format(sql, param.costCenterCode, param.date);
                List<productDescData> productTypeOption = SqlServerHelper.GetEntityList<productDescData>(SqlServerHelper.salesorderConn(), sql);
                return productTypeOption;
            }
            catch (Exception ex)
            {
                return null;
            }
        }
    }
 }